package com.deng.performance.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.impl.FunctionImpl;
import com.deng.common.dao.impl.OrganizationImpl;
import com.deng.performance.dao.IOrganizationFirstBonus;
import com.deng.performance.data.ComparatorOrganizationDesc;
import com.deng.performance.vo.DispenseBonusVo;
import com.deng.performance.vo.OrganizationFirstBonusVo;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;
import com.deng.util.GlobalProperties;
import com.microsoft.sqlserver.jdbc.SQLServerException;

public class OrganizationFirstBonusImpl extends SQLHelper implements
		IOrganizationFirstBonus {

	public List<OrganizationFirstBonusVo> calculateSomeOrganizationFirstBonus(
			List<OrganizationFirstBonusVo> list,String statMonth) {
		// TODO Auto-generated method stub
		if(list == null || list.size() == 0){
			return null;
		}
		try{
			Connection conn = getConnect();
			for(int i =0;i< list.size();i++){
				OrganizationFirstBonusVo ob = list.get(i);
				int organization_id = ob.organization_id;
//				Date stat_time = ob.stat_time;
				
				ResultSet rs1 = queryData(conn,"select base_bonus_function," +
						"	base_bonus_function_detail " +
						"	from t_sys_stat_department " +
						"	where id = "+organization_id);
				String base_bonus_function = "";
				String base_bonus_function_detail = "";
				while(rs1.next()){
					base_bonus_function_detail = rs1.getString("base_bonus_function_detail");
					base_bonus_function = rs1.getString("base_bonus_function");
					if(base_bonus_function_detail != null){
						base_bonus_function_detail = base_bonus_function_detail.replaceAll(GlobalProperties._CUSTOMER_BASE_BONUS, base_bonus_function);
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", ob.organization_id+"");
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+statMonth+"'");
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", ob.duty_id+"");
						try{
							String calSTR = "select "+base_bonus_function_detail+" as x";
							calSTR = calSTR.replaceAll("\\[", "(").replaceAll("\\]", ")")
								.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
								.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/");
							ResultSet rs = queryData(conn,calSTR);
							while(rs.next()){
								Object cal_bonus_ob = rs.getBigDecimal("x");
								if(cal_bonus_ob != null){
//									ob.cal_bonus = cal_bonus_ob.toString();
									ob.cal_value = cal_bonus_ob.toString();
								}
								break;
							}
						}catch(Exception ex2){
							ex2.printStackTrace();
						}
						break;
					}
				}
			}
			freeConnection(conn);
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<OrganizationFirstBonusVo> getOrganizationBonus(
			String statMonth) {
		try{
			Connection conn = getConnect();
			String sql = "	select 	a.name,	a.parent_id,	a.is_leaf,a.id,a.newCode," +
					"	dbo.GetFullOrganizationName(a.id) as fName," +
					"	b.base_bonus_function_detail as old_function," +
					"	a.base_bonus_function_detail as new_function," +
					"	b.cal_value,b.put_value,b.pay_value,b.valid,b.stat_time,	" +
					"	a.duty_id," +
					" (select sum(value) from JIANGCHENG_VALUE as p " +
					" where p.organization_id = a.id and p.stat_time = b.stat_time and p.jiangcheng_type=2) as jiangli," +
					" (select sum(value) from JIANGCHENG_VALUE as p" +
					" where p.organization_id = a.id and p.stat_time = b.stat_time and p.jiangcheng_type=3) as chengfa," +
					" (select sum(value) from JIANGCHENG_VALUE as p" +
					" where p.organization_id = a.id and p.stat_time = b.stat_time and p.jiangcheng_type=1) as bufa"+
					"	from t_sys_stat_department  as a" +
					"	left join t_stat_department_month as b" +
					"	on a.id = b.organization_id" +
					"	and b.stat_time = '"+statMonth+"'" +
					"	where a.is_leaf = 1	order by fName	";
			ResultSet rs = queryData(conn,sql);
			List<OrganizationFirstBonusVo> lst = new ArrayList<OrganizationFirstBonusVo>();
			while(rs.next()){
				OrganizationFirstBonusVo vo = new OrganizationFirstBonusVo();
				
				vo.duty_id = rs.getInt("duty_id");
				vo.organization_id = rs.getInt("id");
				vo.organization_name = rs.getString("name");
				vo.new_code = rs.getString("newCode");

				vo.stat_time = DateTools.stdJustDate(statMonth);
				vo.stat_time_str = statMonth;
				if(rs.getBigDecimal("pay_value")!=null && !rs.getBigDecimal("pay_value").equals("")){
					vo.pay_value = rs.getBigDecimal("pay_value").toString();
				}else{
					vo.pay_value = null;;
				}
				if(rs.getBigDecimal("cal_value")!=null && !rs.getBigDecimal("cal_value").equals("")){
					vo.cal_value = rs.getBigDecimal("cal_value").toString();
				}else{
					vo.cal_value = null;;
				}
				if(rs.getBigDecimal("put_value")!=null && !rs.getBigDecimal("put_value").equals("")){
					vo.put_value = rs.getBigDecimal("put_value").toString();
				}else{
					vo.put_value = null;;
				}
				vo.valid = rs.getInt("valid");				
				vo.full_name = rs.getString("fName");//new OrganizationImpl().getFullOrganizationNameById(vo.organization_id);
				vo.old_function = rs.getString("old_function");
				vo.new_function = rs.getString("new_function");
				
				BigDecimal jiangli = rs.getBigDecimal("jiangli");
				BigDecimal chengfa = rs.getBigDecimal("chengfa");
				BigDecimal bufa = rs.getBigDecimal("bufa");
				BigDecimal final_value = DataFormater.sqlBigDecimal(vo.cal_value)
								.add(DataFormater.sqlBigDecimal(jiangli))
								.multiply(DataFormater.sqlBigDecimal(chengfa))
								.add(DataFormater.sqlBigDecimal(bufa));
				vo.jiangli = DataFormater.sqlBigDecimal(jiangli).toString();
				vo.chengfa = DataFormater.sqlBigDecimal(chengfa).toString();
				vo.bufa = DataFormater.sqlBigDecimal(bufa).toString();
				vo.final_value = final_value.toString();
				lst.add(vo);
			}
			freeConnection(conn);
			return lst;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	
//	public List<OrganizationFirstBonusVo> updateOrganizationRecentFirstBonus(
//			List<OrganizationFirstBonusVo> list,OrganizationFirstBonusVo sVo) {
//		try{
//			if(list != null && list.size() > 0){
//				OrganizationFirstBonusVo ob = list.get(0);
//				String statDate = DateTools.dtsWithTimeT0(ob.stat_time);
//				String createDate = DateTools.dtsWithTime(new Date());
//				Connection conn = getConnect();
//				for(int i =0;i< list.size();i++){
//					ob = list.get(i);
//					if(sVo.organization_id == ob.organization_id){
//						StringBuffer sql = new StringBuffer("update t_stat_organization_recent " +
//								"	set " +
//								"	create_time = '"+createDate+"'");
//							
//							if(ob.cal_bonus != null){
//								sql.append(" ,performance ="+ob.cal_bonus);
//							}
//							if(ob.new_performance_type != null){
//								sql.append(" ,performance_type ="+ob.new_performance_type);
//							}
//							if(ob.new_performance_type_name != null){
//								sql.append(" ,performance_type_name = '"+ob.new_performance_type_name+"'");
//							}
//							if(ob.new_function_name != null){
//								sql.append(" ,function_name = '"+ob.new_function_name+"'");
//							}
//							if(ob.cal_total_point != null){
//								sql.append(" ,total_point = "+ob.cal_total_point);
//							}
//							if(ob.cal_bonus_per_point != null){
//								sql.append(" ,bonus_per_point = "+ob.cal_bonus_per_point);
//							}
//							sql.append("	where organization_id = "+ob.organization_id+
//									"	and stat_time = '"+statDate+"'");
//							
//							int rs = updateData(conn,sql.toString());					
//							if(rs == 0){
//								insertOrganizationFirstBonus(ob);
//							}
//					}
//					
//				}
//				freeConnection(conn);
//				updateOrganizationRecordMonthFirstBonus(statDate);
//				return getOrganizationBonus(statDate);
//			}
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return null;
//	}

//	private int insertOrganizationFirstBonus(OrganizationFirstBonusVo vo){
//		try{
//			StringBuffer sb =new StringBuffer("insert into t_stat_organization_recent  " +
//						"	(organization_id,create_time,stat_time," +
//						"	performance,performance_type," +
//						"	performance_type_name,function_name," +
//						"	total_point,bonus_per_point)" +
//						" 	values" +
//						"	(" +vo.organization_id+",'"+DateTools.dtsWithTimeT0(new Date())+"','"+DateTools.dtsWithTimeT0(vo.stat_time)+"'");
//			if(vo.cal_bonus!=null){
//				sb.append(","+vo.cal_bonus);
//			}else{
//				sb.append(",0");
//			}
//			
//			if(vo.new_performance_type != null){
//				sb.append(","+vo.new_performance_type);
//			}else{
//				sb.append(",0");
//			}
//			
//			if(vo.new_performance_type_name != null){
//				sb.append(",'"+vo.new_performance_type_name+"'");
//			}else{
//				sb.append(",NULL");
//			}
//			
//			if(vo.new_function_name != null){
//				sb.append(",'"+vo.new_function_name+"'");
//			}else{
//				sb.append(",NULL");
//			}
//			
//			if(vo.cal_total_point != null){
//				sb.append(","+vo.cal_total_point);
//			}else{
//				sb.append(",0");
//			}
//			
//			if(vo.cal_bonus_per_point != null){
//				sb.append(","+vo.cal_bonus_per_point);
//			}else{
//				sb.append(",0");
//			}
//			sb.append(")");
//			Connection conn = getConnect();
//			int rs = updateData(conn,sb.toString());
////			freeConnection(conn);
//			return rs;
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return 0;
//	}

	public String getFormulaWithValue(OrganizationFirstBonusVo ob,String statMonth) {
		try{
			System.out.println("演算：  "+statMonth+"  "+ob.full_name);
			Connection conn = getConnect();
			ResultSet rs1 = queryData(conn,"select " +
					"	base_bonus_function_detail " +
					"	from t_sys_stat_department " +
					"	where id = "+ob.organization_id);
			String base_bonus_function_detail = "";
			if(rs1.next()){
				base_bonus_function_detail = rs1.getString("base_bonus_function_detail");
				if(base_bonus_function_detail != null){
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", ob.organization_id+"");
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+statMonth+"'");
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", ob.duty_id+"");
					String rtFirst = base_bonus_function_detail;
					while(base_bonus_function_detail.indexOf("dbo") != -1){
						int startInt = base_bonus_function_detail.indexOf("dbo");
						System.out.println("START: "+startInt);
						int endInt = base_bonus_function_detail.indexOf(")", startInt);
						System.out.println("END: "+endInt);
						String tmpStr = base_bonus_function_detail.substring(startInt, endInt+1);
						
						ResultSet rs2 = queryData(conn,"select "+tmpStr);
						if(rs2.next()){							
							tmpStr = tmpStr.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)");
							base_bonus_function_detail = base_bonus_function_detail.replaceFirst(tmpStr, rs2.getBigDecimal(1).toString());
						}
					}
					freeConnection(conn);
					return rtFirst+" /r/n "+base_bonus_function_detail;
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return null;
	}

	public List<OrganizationFirstBonusVo> calculateFirstBonusForDuty(
			List<OrganizationFirstBonusVo> list, String statMonth) {
		// TODO Auto-generated method stub
		return null;
	}

	
	public DispenseBonusVo getDispenseFirstBonus(DispenseBonusVo vo) {//获得实发的一次分配
		try{
			Connection conn = getConnect();
			String sql = "select * from t_sys_stat_organization_vs_dispense_organization where id = "+vo.dispense_id;
			ResultSet rs1 = queryData(conn,sql);
			if(rs1.next()){
				String related_organization_id = rs1.getString("related_organization_id");
				String s[] = related_organization_id.split(",");
				BigDecimal totalBonus = new BigDecimal(0);
				for(int i =0;i< s.length;i++){
					ResultSet rs = queryData(conn,"select *" +
							"	from t_sys_stat_department" +
							"	where id = "+Integer.valueOf(s[i]));
					if(rs.next()){
						String str = rs.getString("base_bonus_function_detail");
						int organization_id = rs.getInt("id");
						int duty_id = rs.getInt("duty_id");
						String sql2 = "select "+str.replaceAll("@organization_id", organization_id+"")
							.replaceAll("@stat_time", "'"+vo.stat_time_str+"'")
							.replaceAll("\\[", "\\(").replaceAll("\\]", "\\)")
							.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
							.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/")
							.replaceAll("@duty_id",duty_id+"").replaceAll("\'\'", "'");
						ResultSet rs2 = queryData(conn,sql2);
						if(rs2.next()){
							BigDecimal nb = rs.getBigDecimal(1);
							if(nb != null){
								totalBonus = totalBonus.add(nb);	
							}
						}
					}
				}
				vo.bonus = totalBonus;
				freeConnection(conn);
				return vo;
			}			
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public void updateOrganizationRecordMonthFirstBonus(String stat_time) {
		try{
			String sql = "insert into t_stat_organization_record_month " +
					"select * from t_stat_organization_recent where stat_time = '"+stat_time+"'";
			Connection conn = getConnect();
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}	
	}

//	public List<OrganizationFirstBonusVo> getAllOrganizationRecordMonthFirstBonus(
//			String statMonth, String createTime) {
//		try{
//			Connection conn = getConnect();
//			ResultSet rs = queryData(conn,"select " +
//					"	a.name," +
//					"	a.parent_id," +
//					"	a.is_leaf," +
//					"	a.id," +
//					"	dbo.GetFullOrganizationName(a.id) as fName," +
//					"	b.performance," +
//					"	b.stat_time," +
//					"	b.total_point," +
//					"	b.bonus_per_point," +
//					"	a.duty_id " +
//					"	from t_sys_stat_department  as a" +
//					"	left join t_stat_organization_record_month as b" +
//					"	on a.id = b.organization_id" +
//					"	and b.stat_time = '"+statMonth+"'" +
//					"	and b.create_time = '"+createTime+"'" +
//					"	where a.is_leaf = 1" +
//					"	order by fName");
//			List<OrganizationFirstBonusVo> lst = new ArrayList<OrganizationFirstBonusVo>();
//			while(rs.next()){
//				OrganizationFirstBonusVo vo = new OrganizationFirstBonusVo();
//				
//				vo.duty_id = rs.getInt("duty_id");
//				vo.organization_id = rs.getInt("id");
//				vo.organization_name = rs.getString("name");
//				vo.stat_time = DateTools.stdJustDate(statMonth);
//				vo.bonus = rs.getBigDecimal("performance");
//				vo.organization_desc = rs.getString("fName");
//				BigDecimal dc  =  rs.getBigDecimal("bonus_per_point");
//				if(dc != null){
//					vo.bonus_per_point = dc.toString();
//				}
//				vo.total_point = rs.getInt("total_point");
//				lst.add(vo);
//			}
//			freeConnection(conn);
//			return lst;
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return null;
//	
//	}

	public List<String> checkOrganizationRecordStatMonthData(String statMonth) {
		try{
			String sql = "select distinct create_time" +
					"	from t_stat_organization_record_month" +
					"	where stat_time = '"+statMonth+"'";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List l = new ArrayList();
			while(rs.next()){
				System.out.println(rs.getTimestamp("create_time"));
				l.add(DateTools.dtsWithTime(rs.getTimestamp("create_time")));
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}	
		return null;
	}

//	public List<OrganizationFirstBonusVo> updateOneOrganizationFirstBonus(
//			List<OrganizationFirstBonusVo> list, OrganizationFirstBonusVo selVo) {
//
//		try{
//			if(list != null && list.size() > 0){
//				OrganizationFirstBonusVo ob = list.get(0);
//				String statDate = DateTools.dtsWithTimeT0(ob.stat_time);
//				String createDate = DateTools.dtsWithTime(new Date());
//				Connection conn = getConnect();
//				for(int i =0;i< list.size();i++){
//					ob = list.get(i);
//					if(ob.organization_id == selVo.organization_id){
//							StringBuffer sql = new StringBuffer("update t_stat_organization_recent " +
//								"	set " +
//								"	create_time = '"+createDate+"'");
//							
//							if(ob.cal_bonus != null){
//								sql.append(" ,performance ="+ob.cal_bonus);
//							}
//							if(ob.new_performance_type != null){
//								sql.append(" ,performance_type ="+ob.new_performance_type);
//							}
//							if(ob.new_performance_type_name != null){
//								sql.append(" ,performance_type_name = '"+ob.new_performance_type_name+"'");
//							}
//							if(ob.new_function_name != null){
//								sql.append(" ,function_name = '"+ob.new_function_name+"'");
//							}
//							if(ob.cal_total_point != null){
//								sql.append(" ,total_point = "+ob.cal_total_point);
//							}
//							if(ob.cal_bonus_per_point != null){
//								sql.append(" ,bonus_per_point = "+ob.cal_bonus_per_point);
//							}
//							sql.append("	where organization_id = "+ob.organization_id+
//									"	and stat_time = '"+statDate+"'");
//							
//							int rs = updateData(conn,sql.toString());					
//							if(rs == 0){
//								insertOrganizationFirstBonus(selVo);
//							}
//							freeConnection(conn);
//							updateOrganizationRecordMonthFirstBonus(statDate);
//							return getOrganizationBonus(statDate);
//					}					
//				}				
//			}
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return null;
//	
//	}

	public void updateOneOrganizationFirstBonusRecordMonth(
			OrganizationFirstBonusVo vo) {
		// TODO Auto-generated method stub
		
	}


//	public OrganizationFirstBonusVo recalByNewFunction(
//			OrganizationFirstBonusVo selVo,int ifUpdate) {//如果有KPI 要查时间，然后update之前的计算值，并且把差值计入当月
//		try{
//			if(selVo.new_function.startsWith("UP_Period_")){
//				
//			}
//			Connection conn = getConnect();
//			String base_bonus_function_detail = selVo.new_function;
//			if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
//				if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
//					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", selVo.organization_id+"");
//					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+DateTools.dtsJustDate(selVo.stat_time)+"'");
//					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", selVo.duty_id+"");
//					
//					String upFunction = getUPFunction(base_bonus_function_detail);
//					BigDecimal rbrvs = calculateAndUpdateRbrvs(conn,selVo,upFunction);
//					if(upFunction!=null){
//						System.out.println("1:"+upFunction+"___");
//						System.out.println("2:"+rbrvs+"___");
//						System.out.println("3:"+base_bonus_function_detail+"___");						
//						base_bonus_function_detail = base_bonus_function_detail.replaceAll(upFunction.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)"), rbrvs+"");
//						System.out.println("4:"+base_bonus_function_detail+"___");
//					}
//					System.out.println("RBRVS值＝"+rbrvs);
//					try{
//						String calSTR = "select "+base_bonus_function_detail+" as x";
//						calSTR = calSTR.replaceAll("\\[", "(").replaceAll("\\]", ")")
//						.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
//						.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/");
//						ResultSet rs = queryData(conn,calSTR);
//						if(rs.next()){
//							BigDecimal cal_bonus_ob = rs.getBigDecimal("x");
//							if(cal_bonus_ob != null){
//								selVo.cal_value = cal_bonus_ob.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
//								selVo.old_function = selVo.new_function;
//								
//								if(ifUpdate == 1){
//									if(rbrvs != null){
//										selVo.rbrvs = rbrvs;
//									}
//									updateOrganizationValue(conn,selVo,selVo.cal_value);
//									updateOldKpiAndBonus(conn,selVo);
//								}								
//								freeConnection(conn);
//								return selVo;
//							}
//						}
//					}catch(Exception ex2){
//						ex2.printStackTrace();
//					}
//				}
//			}else{
//				System.out.println("本科室：  "+selVo.organization_id+"  "+selVo.full_name+"  base_bonus_function_detail  空");
//			}
//			freeConnection(conn);
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return null;
//	}
	public OrganizationFirstBonusVo recalByNewFunction(
			Connection conn,OrganizationFirstBonusVo selVo,int ifUpdate) {//如果有KPI 要查时间，然后update之前的计算值，并且把差值计入当月
		try{
			if(selVo.new_function == null)
			{
				System.out.println("新公式未设置");
				return selVo;
			}
//			if(selVo.new_function.startsWith("UP_Period_")){
//				
//			}
			String base_bonus_function_detail = selVo.new_function;
			System.out.println(selVo.full_name+"  "+selVo.new_function);
			if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){

				base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", selVo.organization_id+"");
				base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+DateTools.dtsJustDate(selVo.stat_time)+"'");
				base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", selVo.duty_id+"");
				
//				String upFunction = getUPFunction(base_bonus_function_detail);
//				BigDecimal rbrvs = new BigDecimal(0);
//				try{
//					rbrvs = calculateAndUpdateRbrvs(conn,selVo,upFunction);
//					if(upFunction!=null){
////						System.out.println("1:"+upFunction+"___");
////						System.out.println("2:"+rbrvs+"___");
////						System.out.println("3:"+base_bonus_function_detail+"___");						
//						base_bonus_function_detail = base_bonus_function_detail.replaceAll(
//								upFunction.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)").replaceAll("\\-", "\\\\-"), 
//								rbrvs+"");
////						System.out.println("4:"+base_bonus_function_detail+"___");
//					}
//					System.out.println("点数 ＝"+rbrvs);
//				}catch(Exception ex){
//					ex.printStackTrace();
//				}
				
				
				try{
					String calSTR = "select "+base_bonus_function_detail+" as x";
					calSTR = calSTR.replaceAll("\\[", "(").replaceAll("\\]", ")")
						.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
						.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/");
					ResultSet rs = queryData(conn,calSTR);
					if(rs.next()){
						BigDecimal cal_bonus_ob = rs.getBigDecimal("x");
						if(cal_bonus_ob != null){
							selVo.cal_value = cal_bonus_ob.setScale(4, BigDecimal.ROUND_HALF_UP).toString();
							selVo.old_function = selVo.new_function;
							
							if(ifUpdate == 1){
//								if(rbrvs != null){
//									selVo.rbrvs = rbrvs;
//								}
								selVo = updateOrganizationValue(conn,selVo,selVo.cal_value);
//								if(selVo.valid != 1)
//								{
//									selVo.put_value = selVo.cal_value;
//									selVo.pay_value = selVo.cal_value;
//								}
//								updateOldKpiAndBonus(conn,selVo);//在公式内部禁止更新KPI，永远取最新的值
							}
							if(calSTR.indexOf("KPI_Period") > 0)
							{
//								selVo = updatePutValueForKPI(conn,selVo);
							}
//							freeConnection(conn);
							return selVo;
						}
					}
					
				}catch(Exception ex2){
					ex2.printStackTrace();
				}
			
			}else{
				System.out.println("本科室：  "+selVo.organization_id+"  "+selVo.full_name+"  base_bonus_function_detail  空");
			}
//			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	private OrganizationFirstBonusVo updatePutValueForKPI(Connection conn,OrganizationFirstBonusVo selVo)//因为KPI，扣除前面2个月的钱
	{
		try
		{
			BigDecimal kpiValue = new BigDecimal(1);
			BigDecimal chazhi = new BigDecimal(0);
			String sql = "select dbo.KPI_Period("+selVo.organization_id+",'"+DateTools.dtsJustDate(selVo.stat_time)+"','0','1')";
			ResultSet rs2 = queryData(conn, sql);
			if(rs2.next())
			{
				kpiValue = rs2.getBigDecimal(1);
			}
			sql = "select sum(cal_value)*(1-"+kpiValue+") from t_stat_department_month" +
					" where year(stat_time) = year("+DateTools.dtsJustDate(selVo.stat_time)+")" +
					" and month(stat_time) in(month("+DateTools.dtsJustDate(selVo.stat_time)+")-1,month("+DateTools.dtsJustDate(selVo.stat_time)+")-2)" +
					" and organization_id = "+selVo.organization_id;//计算值×KPI
			rs2 = queryData(conn, sql);
			if(rs2.next())
			{
				chazhi = rs2.getBigDecimal(1);
			}
			sql = "update t_stat_department_month" +
					" set put_value = cal_value-"+chazhi+","+
					" pay_value = cal_value-"+chazhi+
					" where organization_id = "+selVo.organization_id+
					" and stat_time = '"+DateTools.dtsJustDate(selVo.stat_time)+"'";
			updateData(conn, sql);
			selVo.put_value = new BigDecimal(selVo.cal_value).subtract(chazhi).setScale(4, BigDecimal.ROUND_HALF_UP).toString();
			selVo.pay_value = selVo.put_value;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return selVo;
	}
	
	private void updateOldKpiAndBonus(Connection conn ,OrganizationFirstBonusVo selVo){
		try{
			String base_bonus_function_detail = selVo.new_function;
			if(base_bonus_function_detail.indexOf("KPI_Period") != -1){
				String sql = "select max(frq_code) as k from KPI_VALUE " +
						"	where dep_id = "+selVo.organization_id+
						"	and year = "+(selVo.stat_time.getYear()+1900);
				ResultSet rs = queryData(conn, sql);
				if(rs.next()){//有其他的   
					if("0001".equals(rs.getString("k")) && selVo.stat_time.getMonth() == 11){//换一整年
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-1-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-2-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-3-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-4-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-5-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-6-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-7-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-8-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-9-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-10-1");
						recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-11-1");
					}if("0002".equals(rs.getString("k"))){//换半年
						if(selVo.stat_time.getMonth() == 5){//上半年
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-1-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-2-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-3-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-4-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-5-1");
						}else if(selVo.stat_time.getMonth() == 11){
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-7-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-8-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-9-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-10-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-11-1");
						}
					}if("0003".equals(rs.getString("k"))){//换一季度
						if(selVo.stat_time.getMonth() == 2){//1jidu
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-1-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-2-1");
						}else if(selVo.stat_time.getMonth() == 5){//2jidu
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-4-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-5-1");							
						}else if(selVo.stat_time.getMonth() == 8){//3jidu
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-7-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-8-1");
						}else if(selVo.stat_time.getMonth() == 11){//4jidu
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-10-1");
							recalByOldFunction(conn,selVo.organization_id,(selVo.stat_time.getYear()+1900)+"-11-1");
						}
					}					
				}				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	private void recalByOldFunction(Connection conn,int organization_id,String stat_time){
		try{
			String sql = "select * from t_stat_department_month where organization_id = "+organization_id +
				" and stat_time = '"+stat_time+"'";
			ResultSet rs = queryData(conn, sql);
			if(rs.next()){
				OrganizationFirstBonusVo selVo = new OrganizationFirstBonusVo();
				selVo.old_function = rs.getString("base_bonus_function_detail");
				selVo.organization_id = organization_id;
				selVo.duty_id = rs.getInt("duty_id");
				selVo.full_name = rs.getString("name");
				
				String base_bonus_function_detail = selVo.old_function;
				if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
					if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", selVo.organization_id+"");
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+stat_time+"'");
						base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", selVo.duty_id+"");
						try{
							String calSTR = "select "+base_bonus_function_detail+" as x";
							calSTR = calSTR.replaceAll("\\[", "(").replaceAll("\\]", ")")
							.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
							.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/");
							ResultSet rs2 = queryData(conn,calSTR);
							if(rs2.next()){
								BigDecimal cal_bonus_ob = rs2.getBigDecimal("x");
								if(cal_bonus_ob != null){
									selVo.cal_value = cal_bonus_ob.setScale(4, BigDecimal.ROUND_HALF_UP).toString();
									updateOrganizationValue(conn,selVo,selVo.cal_value);
								}
							}
						}catch(Exception ex2){
							ex2.printStackTrace();
						}
					}
				}
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}	
	}
	
	
	public OrganizationFirstBonusVo recalByOldFunction(
			OrganizationFirstBonusVo selVo) {
		try{
			Connection conn = getConnect();
			String base_bonus_function_detail = selVo.old_function;
			if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
				if(base_bonus_function_detail != null && !base_bonus_function_detail.equals("")){
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@organization_id", selVo.organization_id+"");
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@stat_time", "'"+DateTools.dtsJustDate(selVo.stat_time)+"'");
					base_bonus_function_detail = base_bonus_function_detail.replaceAll("@duty_id", selVo.duty_id+"");
					try{
						String calSTR = "select "+base_bonus_function_detail+" as x";
						calSTR = calSTR.replaceAll("\\[", "(").replaceAll("\\]", ")")
						.replaceAll("\\＋", "\\+").replaceAll("\\－", "\\-")
						.replaceAll("\\×", "\\*").replaceAll("\\÷", "\\/");
						ResultSet rs = queryData(conn,calSTR);
						if(rs.next()){
							BigDecimal cal_bonus_ob = rs.getBigDecimal("x");
							if(cal_bonus_ob != null){
								selVo.cal_value = cal_bonus_ob.setScale(4, BigDecimal.ROUND_HALF_UP).toString();
								updateOrganizationValue(conn,selVo,selVo.cal_value);
								freeConnection(conn);
								return selVo;
							}
						}
					}catch(Exception ex2){
						ex2.printStackTrace();
					}
				}
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	private OrganizationFirstBonusVo updateOrganizationValue(Connection conn,OrganizationFirstBonusVo selVo,
			String newValue) {
		try{
			if(newValue == null || newValue.equals("")){
				return selVo;
			}
			String sql = "select * from t_stat_department_month" +
						"	where stat_time = '"+DateTools.dtsJustDate(selVo.stat_time)+"'" +
						"	and organization_id = "+selVo.organization_id;
			System.out.println(sql);
			PreparedStatement pst = 
				conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rsx = pst.executeQuery();
			if(rsx.next()){
//				=============================SQL2005
//				if(rsx.getInt("valid") == 1){
//					rsx.updateBigDecimal("cal_value", new BigDecimal(newValue));
//				}else{
//					rsx.updateBigDecimal("cal_value", new BigDecimal(newValue));
//					rsx.updateBigDecimal("pay_value", new BigDecimal(newValue));
//					rsx.updateBigDecimal("rbrvs", selVo.rbrvs);
//				}
//				rsx.updateRow();
//				=============================SQL2000
				String updateSql = "";
				if(rsx.getInt("valid") == 0){
					updateSql = "update t_stat_department_month set " +
							"cal_value="+newValue+"," +
							"put_value="+newValue+"," +
							"pay_value="+newValue+" " +
//							"rbrvs="+selVo.rbrvs+" " +
							" where stat_time = '"+DateTools.dtsJustDate(selVo.stat_time)+"'" +
							" and organization_id = "+selVo.organization_id; 
					updateData(conn, updateSql);
					selVo.pay_value = newValue;
					selVo.cal_value = newValue;
					selVo.put_value = newValue;
				}else{
					updateSql = "update t_stat_department_month set " +
						"cal_value="+newValue+
						" where stat_time = '"+DateTools.dtsJustDate(selVo.stat_time)+"'" +
						" and organization_id = "+selVo.organization_id; 
					updateData(conn, updateSql);
					selVo.cal_value = newValue;
				}
			}else{				
				sql = "insert into t_stat_department_month " +
						"	(organization_id,stat_time," +
						"	name,sjly," +
						"	base_bonus_function_detail,duty_id," +
						"	cal_value,put_value,pay_value," +
						"	valid,rbrvs)" +
						"	values("+selVo.organization_id+",'"+DateTools.dtsJustDate(selVo.stat_time)+"'," +
						"	'"+selVo.full_name+"',1," +
						"	'"+selVo.old_function.replaceAll("'", "''")+"',"+selVo.duty_id+"," +
						"	"+newValue+","+newValue+","+newValue+",0,NULL)";
				updateData(conn, sql);
				selVo.pay_value = newValue;
				selVo.cal_value = newValue;
				selVo.put_value = newValue;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}	
		return selVo;
	}

	private String getUPFunction(String s){
		try{
			if(s.indexOf("dbo.UP_Period_") > -1){
				System.out.println(s);
				int x = s.indexOf("dbo.UP_Period_");
				int y = s.indexOf(")", x);				
				String rtS = s.substring(x,y+1);
				return rtS;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}		
		return s;
	}
	private BigDecimal calculateAndUpdateRbrvs(Connection conn,OrganizationFirstBonusVo vo,String up){
		try{			
			if(up == null){
				return new BigDecimal(0);
			}
			String sql = "select "+up;
			ResultSet rs = queryData(conn, sql);
			if(rs == null){
				return new BigDecimal(0); 
			}
			if(rs.next()){
				BigDecimal rbrvs = rs.getBigDecimal(1);
				return rbrvs;				
			}
		}catch(Exception ex){
			System.out.println(vo.organization_id+" "+vo.full_name+"  ，可能不存在点数公式，因此没有测算出RBRVS");
			return new BigDecimal(0);
//			ex.printStackTrace();
		}
		return new BigDecimal(0);
	}

	public String updateOrganizationBonusAndDispense(List<OrganizationFirstBonusVo> lst) {
		String returnS = "";
		try{	
			String stat_time = "";
			String sql = "";
			Connection conn = getConnect();
			for(int i = 0;i< lst.size();i++)
			{
				OrganizationFirstBonusVo vo = lst.get(i);
				stat_time = DateTools.dtsJustDate(vo.stat_time);
				sql = "update t_stat_department_month" +
					" set  put_value = " +DataFormater.valueToSQL0(vo.put_value)+
					" where organization_id = "+vo.organization_id+
					" and stat_time = '"+stat_time+"'";			
				updateData(conn,sql);
				sql = "update t_stat_department_month" +
					" set  pay_value = put_value" +
					" where organization_id = "+vo.organization_id+
					" and stat_time = '"+stat_time+"'" +
					" and valid != 1";			
				updateData(conn,sql);
			}
			
			
			
			
			
			
			sql = "update t_stat_dispense_month" +
				" set put_value = (" +
				" select sum(put_value) from t_stat_department_month where organization_id in" +
				" 	(" +
				" 	select stat_id from t_sys_stat_organization_vs_dispense_organization where dispense_id = t_stat_dispense_month.dispense_id" +
				" 	)" +
				" and t_stat_department_month.stat_time = t_stat_dispense_month.stat_time" +
				" ) where stat_time = '"+stat_time+"'";
			updateData(conn,sql);
			
			sql = "update t_stat_dispense_month" +
				" set pay_value = put_value where stat_time = '"+stat_time+"' and valid != 1";
			updateData(conn,sql);
			
			freeConnection(conn);
			return null;
		}catch(Exception ex){
			ex.printStackTrace();
		}	
		return returnS;
	}

	public void updateOrgCalValue(OrganizationFirstBonusVo vo,String newValue) {
		try{	
			Connection conn = getConnect();
			String sql = "update t_stat_department_month" +
					" 	set cal_value = "+newValue+", org_value = "+vo.cal_value+
					"	where stat_time = "+DataFormater.valueToSQL(vo.stat_time_str)+
					"	and organization_id = "+vo.organization_id;
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

}
